package edu.unindra.elearning.db;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;

public class ResourceDatabase {

	private static final String DRIVER = "com.mysql.jdbc.Driver";

	private static ResourceDatabase resource = null;

	private Connection connection = null;

	private ResourceDatabase() throws Exception {
		String userName = "root";
		String password = "5678";

		Class.forName(DRIVER);

		String url = "jdbc:mysql://localhost:3306/elearning";
		connection = DriverManager.getConnection(url, userName, password);
	}

	public static ResourceDatabase getInstance() throws Exception {
		if (resource == null) {
			resource = new ResourceDatabase();
		}
		return resource;
	}

	public int executeUpdate(String query) {
		int result = -1;

		Statement statement = null;

		try {
			statement = connection.createStatement();
			result = statement.executeUpdate(query);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				System.out.println("Problem executing query : " + query + " : "
						+ e.getMessage());
			}
		}

		return result;
	}

	public List<Object[]> executeQueryByObject(String query) {
		ResultSet resultSet = null;
		PreparedStatement statement = null;
		LinkedList<Object[]> list = new LinkedList<Object[]>();
		try {
			statement = connection.prepareStatement(query);
			resultSet = statement.executeQuery();

			ResultSetMetaData metaData = resultSet.getMetaData();

			while (resultSet.next()) {
				Object[] fields = new Object[metaData.getColumnCount()];
				for (int i = 0; i < fields.length; i++) {
					fields[i] = resultSet.getObject(i + 1);
				}
				list.addLast(fields);
			}
		} catch (Exception e) {
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (statement != null) {
					statement.close();
				}

			} catch (SQLException e) {
				System.out.println("Problem executing query : " + query + " : "
						+ e.getMessage());
			}
		}
		return list;
	}

	public String executeQueryByString(String query) {
		String str = "";

		ResultSet resultSet = null;
		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(query);
			resultSet = statement.executeQuery();

			while (resultSet.next()) {
				str = (String) resultSet.getObject(1);

			}
		} catch (Exception e) {
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (statement != null) {
					statement.close();
				}

			} catch (SQLException e) {
				System.out.println("Problem executing query : " + query + " : "
						+ e.getMessage());
			}
		}
		return str;
	}

	/**
	 * Method untuk menghentikan koneksi database.
	 */
	public void close() {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public boolean login(String userName, String password) {
		String query = "SELECT * FROM operator WHERE ID_OPERATOR = '"
				+ userName + "' AND PASSWORD_OPERATOR = '" + password + "'";
		List<Object[]> list = executeQueryByObject(query);
		if (list.size() > 0) {
			return true;
		}
		return false;
	}

	public List<Object[]> getOperator() {
		return executeQueryByObject("select * from operator");
	}

	public String getPathSoal(String kls, String mtr) {
		return executeQueryByString("select path from materi where TipeKelas = '"
				+ kls + "' and TipeMateri = '" + mtr + "' and Status = '1' ");
	}

	public List<Object[]> getReports(String tgl) {
		return executeQueryByObject("select TipeMateri,TipeKelas,Nama,NoInduk,Kelas,Score from reports where TglProcess > '"
				+ tgl + "' Order by Kelas ");
	}

	public List<Object[]> getReports(String TipeMateri, String TipeKelas,
			String fDate, String lDate) {
		return executeQueryByObject("SELECT Nama,NoIduk,Kelas,Score FROM Reports WHERE TipeMateri = '"
				+ TipeMateri
				+ "' AND TipeKelas = '"
				+ TipeKelas
				+ "' AND DATE_FORMAT(STR_TO_DATE(TglProcess, '%e-%b-%Y'), '%Y-%m-%e') BETWEEN '"
				+ fDate + "' AND '" + lDate + "'");
	}

	public int insertReports(List<?> list) {
		String query = "Insert into reports values ('" + list.get(0) + "','"
				+ list.get(1) + "','" + list.get(2) + "','" + list.get(3)
				+ "','" + list.get(4) + "','" + list.get(5) + "','"
				+ list.get(6) + "','" + list.get(7) + "')";

		return executeUpdate(query);
	}

	public int insertOperator(List<?> list) {
		String query = "Insert into operator values ('" + list.get(0) + "','"
				+ list.get(1) + "','" + list.get(2) + "','" + list.get(3)
				+ "','" + list.get(4) + "')";

		return executeUpdate(query);
	}

	public int deleteOperator(String id_Operator) {
		String query = "Delete from operator where id_operator = '"
				+ id_Operator + "'";

		return executeUpdate(query);
	}

	public int updateOperator(List<?> list) {
		String query = "update operator set password_operator = '"
				+ list.get(1) + "',nama_operator ='" + list.get(2)
				+ "',alamat_operator ='" + list.get(3)
				+ "',kontak_operator = '" + list.get(4)
				+ "' where id_operator = '" + list.get(0) + "'";

		return executeUpdate(query);
	}

	public List<Object[]> getMateri() {
		return executeQueryByObject("select * from materi");
	}

	public int deleteMateri(String materi, String kelas) {
		String query = "Delete from materi where TipeMateri = '" + materi
				+ "' and TipeKelas = '" + kelas + "'";

		return executeUpdate(query);
	}

	public int insertMateri(List<String> list) {
		String query = "Insert into materi values ('" + list.get(0) + "','"
				+ list.get(1) + "','" + list.get(2) + "','" + list.get(3)
				+ "')";

		return executeUpdate(query);
	}

	public int updateMateri(List<String> list) {
		String query = "update Materi set TipeMateri = '" + list.get(0)
				+ "',TipeKelas ='" + list.get(1) + "',Path ='" + list.get(2)
				+ "',Status = '" + list.get(3) + "' where TipeMateri = '"
				+ list.get(0) + "' and TipeKelas = '" + list.get(1) + "'";

		return executeUpdate(query);
	}
}
